﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class WebSite_m_data_out : System.Web.UI.Page
{
    string CollegeName, Grade, sex, Nationality, Year, Location;
    DataSet ds;
    struct DATASET { public static DataSet ds;}
    string userid;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session["UserId"] == null || Session["UserIdentity"].ToString() != "2")
        {
            Response.Redirect("Default.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }
        else userid = Session["UserId"].ToString();
        

        CollegeName = "";
        Grade = "";
        sex = "";
        Nationality = "";
        Year = "";
        Location = "";
        return;
    }
   private void DropDownList1_ReadCollegeNameXML()
    {
        DropDownList dp = (DropDownList)this.FindControl("DropDownList1");
        string path = Server.MapPath("CollegeName.xml");
        ReadXml(path, dp, "id", "College");
    }
    public void ReadXml(string path, DropDownList dp, string id, string val)
    {
        try
        {
            DataSet ds = new DataSet();
            ds.ReadXml(path);
            dp.DataSource = ds;
            dp.DataTextField = val;
            dp.DataValueField = id;
            dp.DataBind();
        }
        catch(Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message;
            Session["AtPage"] = "m_data_out.aspx";
            Response.Redirect("m_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {

        try
        {
            //绑定数据库，并做遍历
            string sqlString = "select * from PhysiqueTestResultView where UserId IN ( ";
            string searchUserId = "select UserId from UserDetail where ";
            CollegeName = DropDownList1.SelectedItem.Text;
            Grade = DropDownList2.SelectedItem.Text;
            sex = DropDownList3.SelectedItem.Text;
            Nationality = DropDownList4.SelectedItem.Text;
            Year = DropDownList5.SelectedItem.Text;
            Location = DropDownList6.SelectedItem.Text;
            CollegeName.Replace(" ", "");
            Grade.Replace(" ", "");
            sex.Replace(" ", "");
            Nationality.Replace(" ", "");
            Year.Replace(" ", "");
            Location.Replace(" ", "");
            if (!Nationality.Equals("全部") && Nationality.Length > 0)
            {
                searchUserId += "Nationality='" + Nationality + "' ";
            }
            else if (Nationality.Equals("全部") || Nationality.Length == 0)
            {
                searchUserId += "Nationality IN (select Nationality from NationalityTable) ";
            }
            if (!CollegeName.Equals("全部院系") && CollegeName.Length > 0)
            {
                searchUserId += "AND CollegeName='" + CollegeName + "' ";
            }
            if (!Grade.Equals("全部") && Grade.Length > 0)
            {
                searchUserId += "AND Grade=" + Grade + " ";
            }
            if (!sex.Equals("全部") && sex.Length > 0)
            {
                if (sex == "男")
                {
                    searchUserId += " AND UserId IN (select UserId from UserInfo where sex=0) ";
                }
                else if (sex == "女")
                {
                    searchUserId += "AND UserId IN (select UserId from UserInfo where sex=1) ";
                }
            }
            if (!Location.Equals("全部") && Location.Length > 0)
            {
                searchUserId += "AND Location IN ( select CityName from ProvinceTable where ProvinceId=(select CityId from ProvinceTable where CityName='" + Location + "'))) ";
            }
            else if (Location == "全部" || Location.Length == 0)
            {
                searchUserId += "AND location IN (select CityName from ProvinceTable)) ";
            }

            sqlString += searchUserId;

            if (!Year.Equals("全部") && Year.Length > 0)
            {
                int year = Int32.Parse(Year);
                DateTime date = new DateTime(year, 1, 1, 0, 0, 0);
                sqlString += " AND TestDate='" + date.ToString("d") + "'";
            }
            BindData(sqlString);
        }
        catch(Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message;
            Session["AtPage"] = "m_data_out.aspx";
            Response.Redirect("m_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }
   }
    private void BindData(string sqlString)
    {
        try
        {
            SqlConnection Sqlconn = new SqlConnection("Data Source=Localhost;Integrated Security=SSPI;Initial Catalog=CSHDB");
            Sqlconn.Open();
            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.Connection = Sqlconn;
            sqlcmd.CommandText = sqlString;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = sqlcmd;
            ds = new DataSet();
            da.Fill(ds, "体测信息");
            Sqlconn.Close();
            da.Dispose();
            ListView1.DataSource = ds;
            DATASET.ds = ds;
            for (int i = 0; i < ds.Tables["体测信息"].Rows.Count; i++)
            {
                ds.Tables["体测信息"].Rows[i]["Other"] = ds.Tables["体测信息"].Rows[i]["Other"].ToString().Trim();

                if (ds.Tables["体测信息"].Rows[i]["Other"].ToString() == "NONE")
                {
                    ds.Tables["体测信息"].Rows[i]["Other"] = "";
                }
            }
            ListView1.DataBind();
        }
        catch(Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message;
            Session["AtPage"] = "m_data_out.aspx";
            Response.Redirect("m_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }
    }
    protected void ListView1_PagePropertiesChanged(object sender, EventArgs e)
    {
        try
        {
            DataPager pager = (DataPager)ListView1.FindControl("DataPager1");
            int PageSize = pager.PageSize;
            int StartRowIndex = ((DataPager)ListView1.FindControl("DataPager1")).StartRowIndex;
            int TotalRowCount = ((DataPager)ListView1.FindControl("DataPager1")).TotalRowCount;
            ((DataPager)ListView1.FindControl("DataPager1")).SetPageProperties(StartRowIndex, PageSize, true);
            ListView1.DataSource = DATASET.ds;
            ListView1.DataBind();
        }
        catch(Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message;
            Session["AtPage"] = "m_data_out.aspx";
            Response.Redirect("m_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }
    }
    protected void OnClickBuildExcel(object sender,EventArgs e)
    {
        try
        {
            ListView1.DataSource = DATASET.ds;
            ListView1.DataBind();
            CreateExcel(DATASET.ds, "DataExport.xls");
        }
        catch (System.Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message;
            Session["AtPage"] = "m_data_out.aspx";
            Response.Redirect("m_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }
    }
    /// <summary>
    /// 将DataSet里面的表格输出到EXCEL文件中，并输出到用户机子上面。
    /// </summary>
    /// <param name="ds">要导出的DataSet 数据集</param>
    /// <param name="FileName">文件名</param>
    public void CreateExcel(DataSet ds, string FileName)
    {
        try
        {
            HttpResponse resp;
            resp = Page.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
            string colHeaders = "", ls_item = "";
            //定义表对象与行对象，同时用DataSet对其值进行初始化
            DataTable dt = ds.Tables[0];
            DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
            int i = 0;
            int cl = dt.Columns.Count;
            //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
            for (i = 0; i < cl; i++)
            {
                if (i == (cl - 1))//最后一列，加n
                {

                    colHeaders += GetName(dt.Columns[i].Caption.ToString()) + "\n";
                }
                else
                {
                    colHeaders += GetName(dt.Columns[i].Caption.ToString()) + "\t";
                }
            }
            resp.Write(colHeaders);
            //向HTTP输出流中写入取得的数据信息
            //逐行处理数据　
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据　　
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))//最后一列，加n
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }
                }
                resp.Write(ls_item);
                ls_item = "";
            }
            resp.End();
        }
        catch(Exception ex)
        {
            //跳转到错误处理页面。
            Session["ErrorMessage"] = ex.Message;
            Session["AtPage"] = "m_data_out.aspx";
            Response.Redirect("m_error.aspx", false);
            HttpContext.Current.ApplicationInstance.CompleteRequest();
            return;
        }
    }
    /// <summary>
    /// 将数据库中键值的英文名称替换为中文名称
    /// </summary>
    /// <param name="ColumnName">英文键值名称</param>
    /// <returns>中文名称</returns>
    private string GetName(string ColumnName)
    {
        string[,] ColumnArray ={{"UserId","学号"},{"TestDate","测试日期"},{"Weight","体重"},{"Height","身高"},
                               {"VitalCapacity","肺活量"},{"Stamina","耐力项目"},{"Strength","柔韧力量项目"},
                               {"Speed","速度灵巧性项目"},{"Other","奖惩项目"},{"RealName","姓名"},{"Nationality","民族"},
                               {"CityName","学生来源地"},{"sex","性别"},{"Sex","性别"}};
        for (int i = 0; i < ColumnArray.GetLength(0);i++ )
        {
            if (ColumnName.Trim()==ColumnArray[i,0])
            {
                return ColumnArray[i,1];
            }
        }
        return ColumnName;
    }
}
